#include "pg_driver.hpp"

#include <fstream>
#include <iostream>
#include <numeric>

#include "pqxx/pqxx"
#include "json/json.hpp"
#include "utils/path_utils.hpp"
#include "fmt/format.h"
#include "data/cfg_adapters.hpp"

using namespace pqxx;

static const UpgradeInfo &GetDatabaseUpgradeInfo (int32_t current_version)
{
    static std::list<UpgradeInfo> infos{
            UpgradeInfo{
                    .version_from = MAKE_VERSION_CODE (1, 0, 0),
                    .version_to = MAKE_VERSION_CODE (2, 0, 0),
                    .statements = {
                            "alter table tags add column tag_level int default 0;", // 2.0.0 版本增加了标签级别的属性
                    }
            },
            UpgradeInfo{
                    .version_from = MAKE_VERSION_CODE (2, 0, 0),
                    .version_to = MAKE_VERSION_CODE (3, 0, 0),
                    .statements = {
                            "create table work_tasks(task_id serial primary key, task_desc text not null, create_date timestamp not null, issue_id int, act_id int, status int default 1, tags varchar(64) default '', display_color_rgba integer not null, last_time_entry int not null default -1, foreign key (issue_id) references redmine_issues(issue_id) on update cascade on delete set null, foreign key (act_id) references redmine_activities(act_id) on update cascade on delete set null);",
                            "create table time_entries(entry_id serial primary key, task_id int, begin_time timestamp not null, end_time timestamp, duration int, exported int not null default 0, foreign key (task_id) references work_tasks(task_id) on update cascade on delete set null);"
                    }
            },
            UpgradeInfo{
                    .version_from = MAKE_VERSION_CODE (3, 0, 0),
                    .version_to = MAKE_VERSION_CODE (4, 0, 0),
                    .statements = {
                            "alter table tags add column tag_disabled int default 0;",  // 4.0.0 版本增加了禁用标签的功能
                    }
            },
            UpgradeInfo{
                    .version_from = MAKE_VERSION_CODE (4, 0, 0),
                    .version_to = MAKE_VERSION_CODE (5, 0, 0),
                    .statements = {
                            "alter table work_items add column priority int check(priority between 0 and 9) default 0;",  // 5.0.0 版本添加事项优先级，用于自定义排序
                    }
            },
    };

    const auto it = std::ranges::find_if (infos, [current_version](const auto &x) {
        return x.version_from == current_version;
    });
    assert (it != end(infos));
    return *it;
}

struct DriverData {
    std::shared_ptr<connection> c;
    CfgItemPtr                  cfg;

    DriverData ()
    {
        {
            // 创建配置结构体
            auto root = CfgGroup::New();
            root->AddItems({
                CfgText::New(NEW_NC(host), "服务器IP"),
                CfgIntegral::New(false, NEW_NC(port), "服务器端口", "一般为5432"),
                CfgText::New(NEW_NC(database), "数据库名称"),
                CfgText::New(NEW_NC(username), "用户名", "登入数据库使用的用户名"),
                CfgText::New(NEW_NC(password), "密码", "登入数据库使用的密码"),
                CfgAction::New([this] {
                    std::ostringstream oss;
                    if (cfg->Save(oss)) {
                        Connect();
                    }
                }, false, "连接"),
            });
            cfg = root;
        }

        auto cfg_file = std::ifstream ("pg_sql.json"_DP, std::ios::in | std::ios::binary);
        if (cfg_file.is_open ())
        {
            const auto json = nlohmann::json::parse (cfg_file);
            try
            {
                json.get_to (*this);
            }
            catch (...)
            {
            }
            cfg_file.close ();
        }
        cfg->Load();

        Connect();
    }

    ~DriverData ()
    {
        auto cfg_file = std::ofstream ("pg_sql.json"_DP, std::ios::out | std::ios::binary | std::ios::trunc);
        if (cfg_file.is_open ())
        {
            const nlohmann::json json = *this;
            cfg_file << json;
            cfg_file.close ();
        }
    }

    bool ConfigValid () const
    {
        return c != nullptr;
    }

    bool Connect ()
    {
        try
        {
            const auto conn = new connection (fmt::format ("host={} user={} dbname={} port={} password={}", host,
                                                           username, database, port, password));
            c.reset (conn);
        }
        catch (std::exception &e)
        {
            std::cout << e.what () << std::endl;
        }
        return c != nullptr;
    }

    std::string host;
    uint16_t    port{5432};
    std::string database;
    std::string username;
    std::string password;

    NLOHMANN_DEFINE_TYPE_INTRUSIVE (DriverData, host, port, database, username, password)
};

PostgresSQLDriver::PostgresSQLDriver () :
    d{new DriverData}
{
}

PostgresSQLDriver::~PostgresSQLDriver ()
{
    delete d;
}

void PostgresSQLDriver::InitConnection ()
{
    d->Connect ();
}

bool PostgresSQLDriver::IsConfigured ()
{
    return d->ConfigValid ();
}

void PostgresSQLDriver::InitTables () const
{
    if (!d->c)
        return;

    // 这里创建的表结构需要匹配 1.0.0 版本的数据
    static const char *table_create_queries[]{
            // RedMineActivities
            "create table if not exists redmine_activities(act_id int primary key, act_name varchar(64) not null unique);",

            // RedMineIssues
            "create table if not exists redmine_issues(issue_id int primary key, issue_name varchar(128) not null unique, project_name varchar(128) not null, assigned_to varchar(32), is_closed int default 0);",

            // Tags
            "create table if not exists tags(tag_id serial primary key, tag_name varchar(32) not null unique, tag_color integer not null);",

            // WorkItems
            "create table if not exists work_items(work_id serial primary key, hour real not null, comment varchar(255) not null, note text, create_date date not null, act_id int, issue_id int, is_uploaded int default 0, foreign key (act_id) references redmine_activities(act_id) on update cascade on delete set null, foreign key (issue_id) references redmine_issues(issue_id) on update cascade on delete set null);",

            // ItemTags
            "create table if not exists work_item_tags(work_id int not null, tag_id int not null, primary key (work_id, tag_id), foreign key (work_id) references work_items(work_id) on update cascade on delete cascade, foreign key (tag_id) references tags(tag_id) on update cascade on delete cascade);",

            // 特殊用途，记录数据版本用的一张表
            "create table if not exists _version_(version_code int not null);",

            nullptr, // end
    };

    work w{*d->c};
    for (auto stmt = table_create_queries; *stmt; ++stmt)
    {
        w.exec (*stmt);
    }
    w.commit ();
}

bool PostgresSQLDriver::KeepAlive ()
{
    if (d->c == nullptr)
        return false;

    try
    {
        work w{*d->c};
        w.exec ("select version();");
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        bool r = d->Connect ();
        if (r)
            InitPreparedStatements ();
        return r;
    }

    return true;
}

std::vector<Tag> PostgresSQLDriver::AllTags () const
{
    assert (d->c != nullptr);
    std::vector<Tag> result;

    work w{*d->c};

    const auto r = w.exec ("select * from tags order by tag_disabled asc, tag_level asc, tag_id asc;");
    result.reserve (r.size ());

    for (auto it = r.begin (); it != r.end (); ++it)
    {
        result.emplace_back(Tag{
                .tag_id = it.at("tag_id").as<int32_t>(),
                .tag_name = it.at("tag_name").as<std::string>(),
                .tag_color = it.at("tag_color").as<uint32_t>() | 0xFF000000, // hack: PostgreSQL 的 int 类型无法存储超过 0x7fffffff 的数字
                .tag_level = it.at("tag_level").as<uint32_t>(),
                .tag_disabled = (bool)it.at("tag_disabled").as<int32_t>(),
        });
    }

    return result;
}

Tag PostgresSQLDriver::NewTag (const std::string &tag_name, uint32_t color, uint32_t level) const
{
    assert (d->c != nullptr);
    Tag result{0, tag_name, color};

    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared1 ("add_tag", tag_name, color & 0x00FFFFFF, level);

        // hack: PostgreSQL 的 int 类型无法存储超过 0x7fffffff 的数字
        const int rid = r[0].as<int> (0);
        w.commit ();
        result.tag_id = rid;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }

    return result;
}

bool PostgresSQLDriver::DeleteTag (int tag_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("delete_tag", tag_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::UpdateTag (Tag &tag) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("update_tag", tag.tag_name, tag.tag_color & 0x00FFFFFF, tag.tag_level, tag.tag_disabled ? 1 : 0, tag.tag_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

std::vector<RedMineActivity> PostgresSQLDriver::AllRedMineActivities () const
{
    assert (d->c != nullptr);

    std::vector<RedMineActivity> result;

    try
    {
        work       w{*d->c};
        const auto r = w.exec ("select * from redmine_activities;");
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (RedMineActivity{
                    .act_id = it.at ("act_id").as<int> (),
                    .act_name = it.at ("act_name").as<std::string> (),
            });
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }

    return result;
}

bool PostgresSQLDriver::DeleteRedMineActivity (int act_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("delete_activity", act_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::UpdateRedMineActivity (RedMineActivity &tag) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("update_activity", tag.act_id, tag.act_name);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

std::vector<RedMineIssue> PostgresSQLDriver::AllRedMineIssues () const
{
    assert (d->c != nullptr);

    std::vector<RedMineIssue> result;

    try
    {
        work       w{*d->c};
        const auto r = w.exec ("select * from redmine_issues order by is_closed;");
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (RedMineIssue{
                    .issue_id = it["issue_id"].as<int> (),
                    .issue_name = it["issue_name"].as<std::string> (),
                    .project_name = it["project_name"].as<std::string> (),
                    .assigned_to = it["assigned_to"].as<std::string> (),
                    .is_closed = static_cast<bool> (it["is_closed"].as<int> ())
            });
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }

    return result;
}

bool PostgresSQLDriver::DeleteRedMineIssue (int issue_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("delete_issue", issue_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::UpdateRedMineIssue (RedMineIssue &tag) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("update_issue", tag.issue_id, tag.issue_name, tag.project_name, tag.assigned_to,
                                         static_cast<int> (tag.is_closed));
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

WorkItem PostgresSQLDriver::NewWorkItem (double             hour, const std::string &comment, const std::string &note,
                                         const std::string &create_date, int32_t priority) const
{
    assert (d->c != nullptr);

    WorkItem item;
    item.work_id     = 0;
    item.issue_id    = 0;
    item.act_id      = 0;
    item.is_uploaded = false;
    item.note        = note;
    item.comment     = comment;
    item.hour        = hour;
    item.create_date = create_date;
    item.priority    = priority;

    try
    {
        work       w{*d->c};
        const auto r   = w.exec_prepared1 ("new_work_item", hour, comment, note, create_date, priority);
        const int  rid = r[0].as<int> (0);
        w.commit ();
        item.work_id = rid;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }

    return item;
}

bool PostgresSQLDriver::SetWorkItemActivity (int work_id, int act_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("work_item_set_activity", act_id, work_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::SetWorkItemIssue (int work_id, int issue_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("work_item_set_issue", issue_id, work_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::SetWorkItemUploaded (int work_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("work_item_set_upload", work_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::UpdateWorkItem (WorkItem &work_item) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("work_item_update", work_item.create_date, work_item.hour, work_item.comment, work_item.note, work_item.priority, work_item.work_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::DeleteWorkItem (int work_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("delete_work_item", work_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::AddItemTag (int work_id, int tag_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("add_item_tag", work_id, tag_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::DeleteItemTag (int work_id, int tag_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("delete_item_tag", work_id, tag_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

bool PostgresSQLDriver::ClearItemTags (int work_id) const
{
    assert (d->c != nullptr);
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared0 ("clear_item_tags", work_id);
        w.commit ();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
        return false;
    }
    return true;
}

std::vector<Tag> PostgresSQLDriver::AllWorkItemTags (int work_id)
{
    assert (d->c != nullptr);
    std::vector<Tag> result;
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared ("get_work_item_tags", work_id);
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (Tag{
                    .tag_id = it.at ("tag_id").as<int32_t> (),
                    .tag_name = it.at ("tag_name").as<std::string> (),
                    .tag_color = it.at ("tag_color").as<uint32_t> () | 0xFF000000,

                    // hack: PostgreSQL 的 int 类型无法存储超过 0x7fffffff 的数字
                    .tag_level = it.at ("tag_level").as<uint32_t> ()
            });
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

std::vector<std::string> PostgresSQLDriver::GetYearList () const
{
    assert (d->c != nullptr);
    std::vector<std::string> result;
    try
    {
        work       w{*d->c};
        const auto r = w.exec ("select to_char(create_date, 'YYYY') as date_year from work_items group by date_year order by date_year;");
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (it[0].as<std::string> ());
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

std::vector<std::string> PostgresSQLDriver::GetMonthList (const std::string &year) const
{
    assert (d->c != nullptr);
    std::vector<std::string> result;
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared ("get_month_list", year);
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (it[0].as<std::string> ());
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

std::vector<std::string> PostgresSQLDriver::GetDayList (const std::string &year_and_month) const
{
    assert (d->c != nullptr);
    std::vector<std::string> result;
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared ("get_day_list", year_and_month);
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (it[0].as<std::string> ());
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

std::vector<WorkItem> PostgresSQLDriver::GetDailyItems (const std::string &date) const
{
    assert (d->c != nullptr);
    std::vector<WorkItem> result;
    try
    {
        work       w{*d->c};
        const auto r = w.exec_prepared ("get_daily_items", date);
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (
                    WorkItem{
                            .work_id = it["work_id"].as<int> (),
                            .hour = it["hour"].as<double> (),
                            .comment = it["comment"].as<std::string> (),
                            .note = it["note"].as<std::string> (),
                            .create_date = it["create_date"].as<std::string> (),
                            .act_id = it["act_id"].as<int> (0),
                            .issue_id = it["issue_id"].as<int> (0),
                            .priority = it["priority"].as<int> (0),
                            .is_uploaded = static_cast<bool> (it["is_uploaded"].as<int> ())
                    }
                    );
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

std::vector<WorkItem> PostgresSQLDriver::GetItemsByTagAndDate (const std::string &date_start,
                                                               const std::string &date_end, int tag_id_l1, int tag_id_l2) const
{
    assert (d->c != nullptr);
    std::vector<WorkItem> result;
    try
    {
        work         w{*d->c};
        pqxx::result r;
        if (tag_id_l2 == 0)
        {
            r = w.exec_prepared ("get_item_by_l1_tag_and_date", tag_id_l1, date_start, date_end);
        }
        else
        {
            r = w.exec_prepared ("get_item_by_tags_and_date", tag_id_l1, tag_id_l2, date_start, date_end);
        }
        result.reserve (r.size ());

        for (const auto &it: r)
        {
            result.emplace_back (
                    WorkItem{
                            .work_id = it["work_id"].as<int> (),
                            .hour = it["hour"].as<double> (),
                            .comment = it["comment"].as<std::string> (),
                            .note = it["note"].as<std::string> (),
                            .create_date = it["create_date"].as<std::string> (),
                            .act_id = it["act_id"].as<int> (0),
                            .issue_id = it["issue_id"].as<int> (0),
                            .priority = it["priority"].as<int> (0),
                            .is_uploaded = static_cast<bool> (it["is_uploaded"].as<int> ())
                    }
                    );
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

StatisticsResult PostgresSQLDriver::GetStatisticsResult (const std::string &date_start,
                                                         const std::string &date_end) const
{
    assert (d->c != nullptr);
    StatisticsResult result{.date_start = date_start, .date_end = date_end, .total_time = 0.0, .tags = {}};

    try
    {
        // total time
        work w{*d->c};
        result.total_time = w.exec_prepared1 ("query_total_time", date_start, date_end)[0].as<double> ();

        // primary tag time
        std::vector<StatisticsResult::TagTime> tags;
        const auto                             r = w.exec_prepared ("query_primary_tag_time", date_start, date_end);
        tags.reserve (r.size ());
        for (const auto &it: r)
        {
            tags.emplace_back (
                    StatisticsResult::TagTime{
                            .tag_id = it[0].as<int32_t> (),
                            .tag_name = it[2].as<std::string> (),
                            .tag_time = it[1].as<double> ()
                    }
                    );
        }
        result.tags = move (tags);

        for (auto &x: result.tags)
        {
            const auto rr = w.exec_prepared ("query_secondary_tag_time", x.tag_id, date_start, date_end);
            x.sub_tags.reserve (rr.size ());
            for (const auto &it: rr)
            {
                x.sub_tags.emplace_back (
                        StatisticsResult::TagTime{
                                .tag_id = it[0].as<int32_t> (),
                                .tag_name = it[2].as<std::string> (),
                                .tag_time = it[1].as<double> ()
                        }
                        );
            }
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }

    return result;
}

StatisticsResult PostgresSQLDriver::GetStatisticsResult () const
{
    assert (d->c != nullptr);
    StatisticsResult result{.total_time = 0.0};
    try
    {
        work        w{*d->c};
        const auto &r = w.exec1 (
                "select t1.create_date as date_begin, t2.create_date as date_end from (select create_date from work_items ORDER by create_date asc limit 1) as t1, (select create_date from work_items ORDER by create_date desc limit 1) as t2;");
        const auto beg = r[0].as<std::string> ();
        const auto end = r[1].as<std::string> ();
        result         = GetStatisticsResult (beg, end);
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

StatisticsResult PostgresSQLDriver::GetStatisticsResult(const std::string &date_start, const std::string &date_end, int tag_id) const 
{
    StatisticsResult result {.total_time = 0.0};

    return result;
}

WorkTask PostgresSQLDriver::NewTask (std::string task_desc, const std::string &create_date, uint32_t color)
{
    WorkTask task{.task_id = 0, .task_desc = task_desc, .create_date = create_date};
    try
    {
        work       w{*d->c};
        auto       row = w.exec_prepared1 ("new_task", task_desc, create_date, static_cast<int> (color));
        auto const rid = row[0].as<int> (0);
        w.commit ();
        task.task_id = rid;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return task;
}

bool PostgresSQLDriver::UpdateTask (const WorkTask &task)
{
    try
    {
        std::string tag_list = std::accumulate (begin (task.tags), end (task.tags), std::string{}, [](auto v, auto x) {
            if (!v.empty ())
                v.push_back (',');
            return v + std::to_string (x);
        });
        work w{*d->c};
        w.exec_prepared ("update_task", task.redmine_act_id, task.redmine_issue_id, tag_list, (int) task.display_color_rgba, task.last_time_entry, task.task_id);
        w.commit ();
        return true;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return false;
}

bool PostgresSQLDriver::DeleteTask (const WorkTask &task)
{
    try
    {
        work w{*d->c};
        w.exec_prepared ("delete_task", task.task_id);
        w.commit ();
        return true;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return false;
}

bool PostgresSQLDriver::SetTaskStatus (WorkTask &task, TaskStatus status)
{
    try
    {
        work w{*d->c};
        w.exec_prepared ("set_task_status", (int) status, task.task_id);
        w.commit ();
        task.status = status;
        return true;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return false;
}

static std::vector<int> to_ints (const std::string &in)
{
    std::vector<int> ret;
    size_t           s = 0, i = 0;
    while ((i = in.find (',', s)) != std::string::npos)
    {
        try
        {
            ret.emplace_back (std::stoi (in.substr (s, i - s)));
            s = i + 1;
        }
        catch (...)
        {
            break;
        }
    }
    if (s < in.length())
    {
        try
        {
            ret.emplace_back(std::stoi(in.substr(s, i - s)));
            s = i + 1;
        } catch (...)
        {}
    }
    return ret;
}

std::vector<WorkTask> PostgresSQLDriver::GetTasks () const
{
    std::vector<WorkTask> result;
    try
    {
        work       w{*d->c};
        const auto r = w.exec ("select * from work_tasks order by status asc, task_id asc;");
        result.reserve (r.size ());
        for (auto it = r.begin (); it != r.end (); ++it)
        {
            WorkTask task{};

            task.task_id            = it.at (0).as<int> ();
            task.task_desc          = it.at (1).as<std::string> ();
            task.create_date        = it.at (2).as<std::string> ();
            task.redmine_issue_id   = it.at (3).as<int> (-1);
            task.redmine_act_id     = it.at (4).as<int> (-1);
            task.status             = static_cast<TaskStatus> (it.at (5).as<int> ());
            task.tags               = to_ints (it.at (6).as<std::string> ());
            task.display_color_rgba = static_cast<uint32_t> (it.at (7).as<int32_t> ());
            task.last_time_entry    = it.at (8).as<int32_t> (-1);

            result.emplace_back (task);
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

WorkTask PostgresSQLDriver::GetCurrentTask () const
{
    WorkTask result{.task_id = -1, .last_time_entry = -1};
    try
    {
        work w{*d->c};
        auto res = w.exec_prepared ("get_current_task", static_cast<int> (eInProgress));
        if (!res.empty ())
        {
            auto r                    = res[0];
            result.task_id            = r.at (0).as<int> ();
            result.task_desc          = r.at (1).as<std::string> ();
            result.create_date        = r.at (2).as<std::string> ();
            result.redmine_issue_id   = r.at (3).as<int> (-1);
            result.redmine_act_id     = r.at (4).as<int> (-1);
            result.status             = static_cast<TaskStatus> (r.at (5).as<int> ());
            result.tags               = to_ints (r.at (6).as<std::string> ());
            result.display_color_rgba = static_cast<uint32_t> (r.at(7).as<int32_t>());
            result.last_time_entry    = r.at (8).as<int32_t> (-1);
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

void PostgresSQLDriver::ClearActiveTask () const
{
    try
    {
        work w{ *d->c };
        w.exec_prepared("clear_active_status", (int)ePaused, (int)eInProgress);
        w.commit();
    }
    catch (std::exception &e)
    {
        std::cerr << e.what() << std::endl;
    }
}

TimeEntry PostgresSQLDriver::NewTimeEntry (int32_t task_id, const std::string &start_date_time)
{
    TimeEntry entry{.entry_id = -1, .task_id = task_id, .begin_time = start_date_time, .duration = 0};
    try
    {
        work      w{*d->c};
        if (task_id > -1)
        {
            auto      row = w.exec_prepared1 ("new_time_entry", task_id, start_date_time);
            const int rid = row[0].as<int> (0);
            w.commit ();
            entry.entry_id = rid;
        }
        else
        {
            auto      row = w.exec_prepared1 ("new_time_entry", nullptr, start_date_time);
            const int rid = row[0].as<int> (0);
            w.commit ();
            entry.entry_id = rid;
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return entry;
}

bool PostgresSQLDriver::UpdateTimeEntry (TimeEntry &entry, const std::string &end_time, int32_t duration)
{
    try
    {
        work w{*d->c};
        w.exec_prepared ("update_time_entry", end_time, duration, entry.entry_id);
        w.commit ();
        entry.duration = duration;
        return true;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return false;
}

void PostgresSQLDriver::MarkTimeEntryAsExported (TimeEntry &entry) const {
    try
    {
        work w{*d->c};
        w.exec_prepared ("mark_entry_exported", entry.entry_id);
        w.commit ();
        entry.exported = 1;
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
}

std::vector<TimeEntry> PostgresSQLDriver::GetTimeEntries (int32_t task_id, const std::string &date_beg, const std::string &date_end)
{
    std::vector<TimeEntry> result;
    try
    {
        std::string datetime_begin = date_beg + " 00:00:00";
        std::string datetime_end = date_beg + " 23:59:59";

        work w{*d->c};

        auto res = w.exec_prepared ("get_task_entries_by_range", task_id, datetime_begin, datetime_end);
        if (!res.empty()) {
            for (auto row: res) {
                TimeEntry entry{};
                entry.entry_id = row[0].as(-1);
                entry.task_id = row[1].as(-1);
                entry.begin_time = row[2].as(""s);
                entry.end_time = row[3].as(""s);
                entry.duration = row[4].as(0);
                entry.exported = row[5].as(0);
                result.emplace_back (std::move(entry));
            }
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}
std::vector<TimeEntry> PostgresSQLDriver::GetTimeEntries (const std::string &date_beg, const std::string &date_end) const
{
    std::vector<TimeEntry> result;
    try
    {
        std::string datetime_begin = date_beg + " 00:00:00";
        std::string datetime_end = date_beg + " 23:59:59";

        work w{*d->c};

        auto res = w.exec_prepared ("get_time_entries_by_range", datetime_begin, datetime_end);
        if (!res.empty()) {
            for (auto row: res) {
                TimeEntry entry{};
                entry.entry_id = row[0].as(-1);
                entry.task_id = row[1].as(-1);
                entry.begin_time = row[2].as(""s);
                entry.end_time = row[3].as(""s);
                entry.duration = row[4].as(0);
                entry.exported = row[5].as(0);
                result.emplace_back (std::move(entry));
            }
        }
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}
TimeEntry PostgresSQLDriver::GetCurrentTimeEntry (int32_t entry_id) const
{
    TimeEntry entry{ .entry_id = -1 };
    try
    {
        work w{ *d->c };
        if (entry_id > -1) {
            auto row = w.exec_prepared1 ("get_entry_by_id", entry_id);
            entry.entry_id = row[0].as<int> (-1);
            entry.task_id = row[1].as<int> (-1);
            entry.begin_time = row[2].as<std::string> ("");
            entry.end_time = row[3].as<std::string> ("");
            entry.duration = row[4].as<int> (-1);
            entry.exported = row[5].as<int> (0);
        }
        else
        {
            auto res = w.exec_prepared ("get_entry_latest");
            if (!res.empty())
            {
                auto row = res[0];
                entry.entry_id   = row[0].as<int> (-1);
                entry.task_id    = row[1].as<int> (-1);
                entry.begin_time = row[2].as<std::string> ("");
                entry.end_time   = row[3].as<std::string> ("");
                entry.duration   = row[4].as<int> (-1);
                entry.exported   = row[5].as<int> (0);
            }
        }
    }
    catch (std::exception& e)
    {
        std::cerr << e.what() << std::endl;
    }
    return entry;
}

CfgItemBase* PostgresSQLDriver::GetConfig() const 
{
    return d->cfg.get();
}

int32_t PostgresSQLDriver::GetDataVersion () const
{
    if (d->c == nullptr)
        return 0x7FFFFFFF;
    int32_t result = MAKE_VERSION_CODE (1, 0, 0); // 默认是第一版数据

    try
    {
        work w{*d->c};

        auto r = w.exec1 ("select version_code from _version_ order by version_code desc limit 1;");
        result = r[0].as<int32_t> (MAKE_VERSION_CODE (1, 0, 0));
    }
    catch (std::exception &e)
    {
        std::cerr << e.what () << std::endl;
    }
    return result;
}

void PostgresSQLDriver::MigrateTables (int32_t old_version) const
{
    if (d->c == nullptr)
        return;

    // 初始化语句
    d->c->prepare ("new_version", "insert into _version_(version_code) values ($1);");

    int32_t current_version = old_version;
    while (current_version != DATA_VERSION)
    {
        const auto &info = GetDatabaseUpgradeInfo (current_version);

        // execute statements
        try
        {
            work w{*d->c};
            for (auto &x: info.statements)
            {
                if (x)
                    w.exec (x);
            }
            w.exec_prepared ("new_version", info.version_to);
            w.commit ();
        }
        catch (std::exception &e)
        {
            std::cerr << e.what () << std::endl;

            // abort();
        }
        current_version = info.version_to;
    }

    current_version = GetDataVersion ();
    assert (current_version == DATA_VERSION);
}

void PostgresSQLDriver::InitPreparedStatements () const
{
    if (d->c == nullptr)
        return;

    // prepared statements
    const auto &c = d->c;
    c->prepare ("add_tag", "insert into tags(tag_name, tag_color, tag_level, tag_disabled) values($1, $2, $3, 0) returning tag_id;");
    c->prepare ("delete_tag", "delete from tags where tag_id = $1;");
    c->prepare ("update_tag", "update tags set tag_name=$1, tag_color=$2, tag_level=$3, tag_disabled=$4 where tag_id=$5;");

    c->prepare ("delete_activity", "delete from redmine_activities where act_id = $1;");
    c->prepare ("update_activity",
                "insert into redmine_activities(act_id, act_name) values ($1,$2) on conflict(act_id) do update set act_name = $2;");

    c->prepare ("delete_issue", "delete from redmine_issues where issue_id=$1;");
    c->prepare ("update_issue",
                "insert into redmine_issues(issue_id, issue_name, project_name, assigned_to, is_closed) values ($1,$2, $3, $4, $5) on conflict(issue_id) do update set issue_name = $2, project_name = $3, assigned_to = $4, is_closed = $5;");

    c->prepare ("new_work_item",
                "insert into work_items(hour, comment, note, create_date, priority) values($1,$2,$3,$4,$5) returning work_id;");
    c->prepare ("work_item_set_activity", "update work_items set act_id=$1 where work_id=$2;");
    c->prepare ("work_item_set_issue", "update work_items set issue_id=$1 where work_id=$2;");
    c->prepare ("work_item_set_upload", "update work_items set is_uploaded=1 where work_id=$1;");
    c->prepare ("work_item_update", "update work_items set create_date=$1,hour=$2,comment=$3,note=$4,priority=$5 where work_id=$6;");
    c->prepare ("delete_work_item", "delete from work_items where work_id=$1;");

    c->prepare ("add_item_tag", "insert into work_item_tags(work_id, tag_id) values($1,$2);");
    c->prepare ("delete_item_tag", "delete from work_item_tags where work_id=$1 and tag_id=$2;");
    c->prepare ("clear_item_tags", "delete from work_item_tags where work_id=$1;");

    c->prepare ("get_work_item_tags",
                "select tags.* from work_item_tags inner join tags on work_item_tags.tag_id=tags.tag_id where work_item_tags.work_id=$1 order by tag_level asc, tag_id asc;");

    c->prepare ("get_month_list",
                "select to_char(create_date, 'MM') as date_month from work_items where to_char(create_date,'YYYY')=$1 group by date_month order by date_month;");
    c->prepare ("get_day_list",
                "select to_char(create_date, 'DD') as date_day from work_items where to_char(create_date, 'YYYY-MM')=$1 group by date_day order by date_day;");

    c->prepare ("get_daily_items", "select * from work_items where to_char(create_date,'YYYY-MM-DD')=$1 order by priority asc, work_id asc;");
    c->prepare ("query_total_time", "select sum(hour) as total from work_items where create_date between $1 and $2;");
    c->prepare ("query_primary_tag_time", R"sql(
select tags.tag_id, sum(hour) as total_hours, tag_name from ((work_item_tags inner join (select work_id, hour from work_items where create_date between $1 and $2) as t1 on work_item_tags.work_id = t1.work_id) as t2 inner join tags on tags.tag_id = t2.tag_id and tags.tag_level = 0) group by tags.tag_id;
)sql");

    c->prepare ("query_secondary_tag_time", R"sql(
select tags.tag_id,

       sum(hour) as total_hours,

       tag_name

  from (((
          (select work_id
           from work_item_tags
           where tag_id = $1) as t0
        inner join work_item_tags on t0.work_id = work_item_tags.work_id
        and work_item_tags.tag_id != $1)
       inner join
         (select work_id,
                 hour
          from work_items
          where create_date between $2 and $3) as t1 on t0.work_id = t1.work_id) as t2
      inner join tags on tags.tag_id = t2.tag_id and tags.tag_level != 0)

 group by tags.tag_id;
)sql");

    c->prepare ("get_item_by_l1_tag_and_date", R"sql(
select work_items.* from (
  work_items inner join work_item_tags on work_items.work_id = work_item_tags.work_id
) where work_item_tags.tag_id=$1 and work_items.create_date between $2 and $3 order by work_items.create_date, work_items.work_id;
)sql");
    c->prepare ("get_item_by_tags_and_date", R"sql(
select work_items.* from work_items right join (select work_item_tags.work_id from (select work_id
           from work_item_tags
           where tag_id = $1) as t0
        inner join work_item_tags on t0.work_id = work_item_tags.work_id
        and work_item_tags.tag_id = $2) as t1 on work_items.work_id = t1.work_id where create_date between $3 and $4 order by work_items.create_date, work_items.work_id;
)sql");

    c->prepare ("new_task", "insert into work_tasks(task_desc, create_date, display_color_rgba) values($1,$2, $3) returning task_id;");
    c->prepare ("update_task", "update work_tasks set act_id=(CASE WHEN $1=-1 THEN NULL ELSE $1 END), issue_id=(CASE WHEN $2=-1 THEN NULL ELSE $2 END), tags=$3, display_color_rgba=$4, last_time_entry=$5 where task_id=$6;");
    c->prepare ("delete_task", "delete from work_tasks where task_id=$1;");
    c->prepare ("set_task_status", "update work_tasks set status=$1 where task_id=$2;");
    c->prepare ("get_current_task", "select * from work_tasks where status=$1;");
    c->prepare ("clear_active_status", "update work_tasks set status=$1 where status=$2;");

    c->prepare ("new_time_entry", "insert into time_entries(task_id, begin_time) values($1, $2) returning entry_id;");
    c->prepare ("update_time_entry", "update time_entries set end_time=$1, duration=$2 where entry_id=$3;");
    c->prepare ("mark_entry_exported", "update time_entries set exported=1 where entry_id=$1;");
    c->prepare ("get_entry_by_id", "select * from time_entries where entry_id=$1;");
    c->prepare ("get_entry_latest", "select * from time_entries where end_time is null order by entry_id desc limit 1;");
    c->prepare ("get_time_entries_by_range", "select * from time_entries where begin_time between $1 and $2 and end_time between $1 and $2;");
    c->prepare ("get_task_entries_by_range", "select * from time_entries where task_id=$1 and begin_time between $2 and $3;");
}
